package com.sdap2.dao;

import com.sdap2.common.dao.LogicDAO;
import com.sdap2.entity.SoilData;
import com.sdap2.dto.SoilDataStatsDTO;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.time.LocalDateTime;
import java.util.List;
import java.util.Optional;

@Repository
public interface SoilDataDAO extends LogicDAO<SoilData, Long> {
    Page<SoilData> findByDeviceIdAndCreateTimeBetween(
        Long deviceId, 
        LocalDateTime startTime,
        LocalDateTime endTime, 
        Pageable pageable
    );

    Optional<SoilData> findFirstByDeviceIdOrderByCreateTimeDesc(Long deviceId);

    void deleteByDeviceId(Long deviceId);

    @Query("SELECT s.name as name, " +
           "MIN(s.value) as minValue, " +
           "MAX(s.value) as maxValue, " +
           "AVG(s.value) as avgValue, " +
           "s.unit as unit, " +
           "MAX(s.createTime) as lastUpdateTime " +
           "FROM SoilData s " +
           "WHERE s.device.id = :deviceId " +
           "AND s.isDeleted = false " +
           "GROUP BY s.name, s.unit")
    List<SoilDataStatsDTO> findStatsByDeviceId(@Param("deviceId") Long deviceId);

    @Query("SELECT s FROM SoilData s " +
           "WHERE s.device.id = :deviceId " +
           "AND s.isDeleted = false " +
           "ORDER BY s.createTime DESC")
    List<SoilData> findAllByDeviceId(@Param("deviceId") Long deviceId);
} 